import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from util import file_helper


def get_df_from_excel_从excel文件中获取df数据(filename, gzb_工作表=None):
    # 读取 Excel 文件
    print(filename)
    wb = load_workbook(filename)

    if gzb_工作表:
        # 选择第一个工作表
        ws = wb[gzb_工作表]
    else:
        ws = wb.active

    # 从工作表中读取数据并转换为 DataFrame
    data = ws.values
    cols = next(data)
    data = list(data)
    df = pd.DataFrame(data, columns=cols)

    return df


def get_sheets_从excel文件中获取sheet列表(filename):
    wb = load_workbook(filename)
    sheets = wb.sheetnames
    return sheets


def write_df_in_xlsx_将df写入xlsx中(
    df: pd.DataFrame, folder_path, file_name, sheet_name="out"
):
    file_helper.cjwjj_创建文件夹(folder_path)
    file_path = f"{folder_path}/{file_name}"
    df.to_excel(file_path, sheet_name=sheet_name, index=False)


def write_df_in_newsheet_将df写入新的sheet中(
    df: pd.DataFrame, folder_path, file_name, new_sheet_name="out"
):
    file_helper.cjwjj_创建文件夹(folder_path)
    # 读取Excel文件
    file_path = f"{folder_path}/{file_name}"
    print(file_path)
    wb = load_workbook(file_path)

    # 如果已经存在同名的工作表，删除它
    if new_sheet_name in wb.sheetnames:
        del wb[new_sheet_name]

    # 创建新的工作表
    new_sheet = wb.create_sheet(title=new_sheet_name)

    # 将DataFrame数据写入新的工作表
    for r_idx, row in enumerate(dataframe_to_rows(df, index=False), 1):
        for c_idx, value in enumerate(row, 1):
            new_sheet.cell(row=r_idx, column=c_idx, value=value)

    # 保存Excel文件
    wb.save(file_path)


def zlexcel_整理excel(folder_path: str, folder_path_out: str):
    list_file_name_xlsx = file_helper.get_files_name_list_获取当前目录下的文件名列表(
        "xlsx", folder_path=folder_path
    )
    file_helper.cjwjj_创建文件夹(folder_path_out)
    json = file_helper.dqjson_读取json文件(f"{folder_path}/excel.json")
    # print(list_xlsx)
    for file_name_xlsx in list_file_name_xlsx:
        # print("ss", file_path_xlsx)
        file_helper.cjwjj_创建文件夹(folder_path_out)
        file_path_xlsx_out = f"{folder_path_out}/{file_name_xlsx}"
        if file_helper.pdwjsfcz_判断文件是否存在(file_path_xlsx_out):
            continue
        file_path_xlsx = f"{folder_path}/{file_name_xlsx}"
        df = get_df_from_excel_从excel文件中获取df数据(file_path_xlsx)
        # file_name_xlsx = file_helper.tqwjm_提取文件名(file_path_xlsx)
        if "删除行" in json:
            sch_删除行 = json["删除行"] - 1
            df.columns = df.iloc[sch_删除行]
            if sch_删除行 >= 0:
                df = df.drop(index=range(sch_删除行 + 1))
        # file_path_xlsx_out = f"{json['file_path_out']}/{file_name_xlsx}"
        df = df.rename(columns=json["替换列"])

        tql_提取列 = json["提取列"]
        df = df[tql_提取列]
        # print(df.columns)
        sheet_name = json["sheet name"]
        write_df_in_xlsx_将df写入xlsx中(
            df=df,
            # folder_path=json["folder_path_out"],
            folder_path=folder_path_out,
            file_name=file_name_xlsx,
            sheet_name=sheet_name,
        )
        # print(df.columns)
